package gwtappcontainer.server.apps.insight;

import gwtappcontainer.server.Limits;
import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.server.apps.security.AccessController;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Center;
import gwtappcontainer.shared.apps.insight.Member;
import gwtappcontainer.shared.apps.insight.Member.ContactDetails;
import gwtappcontainer.shared.apps.insight.SyntaxChecker;
import gwtappcontainer.shared.apps.insight.UnlistedProgram;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.google.gwt.dev.util.collect.HashMap;
import com.google.gwt.dev.util.collect.HashSet;

class MemberRepository {
	
	public static Member get(String email, String login) {				
		return getByIdOrEmail(0, email, login);	
	}
	
	public static Member get(int memberId, String login) {				
		return getByIdOrEmail(memberId, null, login);	
	}
	
	private static Member getByIdOrEmail(int memberId, String email, String login) {
		
		if ((memberId == 0) && (email == null))
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Either id or email has to be specified");					
		
    	String sql;
    	if (memberId == 0)
    		sql = "select member_id, first_name, middle_name, last_name, email, " +
        		"phone, home_address, office_address, home_phone, office_phone, singapore_nric " + 
        		"from members, centers where email = ?";
    	else {
    		sql = "select member_id, first_name, middle_name, last_name, email, " +
            		"phone, home_address, office_address, home_phone, office_phone, singapore_nric " + 
            		"from members, centers where member_id = ?";
		}       
       
    	Member member = null;

        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
            try (PreparedStatement ps = connection.prepareStatement(sql)) {   
            	if (memberId == 0)
            		ps.setString(1, email.toLowerCase());                		
            	else {
            		ps.setInt(1, memberId);						
				}
               
                try (ResultSet resultSet = ps.executeQuery()) {                                        
                    while (resultSet.next()) {
                    	member = new Member();
                        
                        member.id = resultSet.getInt("member_id");
                        member.contactDetails.firstName = resultSet.getString("first_name");                       
                        member.contactDetails.lastName = resultSet.getString("last_name");
                        member.contactDetails.middleName = resultSet.getString("middle_name");    
                        member.contactDetails.email = resultSet.getString("email");
                        member.contactDetails.phone = resultSet.getString("phone");
                        member.contactDetails.homeAddress = resultSet.getString("home_address");
                        member.contactDetails.officeAddress = resultSet.getString("office_address");
                        member.contactDetails.homePhone = resultSet.getString("home_phone");
                        member.contactDetails.officePhone = resultSet.getString("office_phone");
                        member.contactDetails.singaporeNRIC = resultSet.getString("singapore_nric");
                    }
                }                                                               
            }        
		} catch (Exception ex) {
	         throw new RuntimeException(ex);
	    }  
            
        if (null == member) 
        	return null;
        
        if (! member.contactDetails.email.equals(login))
        	AccessController.ensureValidUser(login);
        
        sql = "select mc.center_id, center, owning_center from member_centers mc, centers c where " +  
    			"member_id = ? and mc.center_id = c.center_id order by center";
        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {    
            try (PreparedStatement ps = connection.prepareStatement(sql)) {   
            	ps.setLong(1, member.id);
               
                try (ResultSet resultSet = ps.executeQuery()) {                                        
                    while (resultSet.next()) {
                       Center center = new Center();
                       center.id = resultSet.getInt(1);
                       center.name = resultSet.getString(2);
                       
                       boolean owningCenter = resultSet.getBoolean(3);
                       if (owningCenter)
                    	   member.owningCenter = center;
                       else 
                    	   member.additionalCenters.add(center);
                    }
                }                                        
            }
            
            sql = "select unlisted_program_id, up.program_type_id, pt.program_type, month, year, teacher, venue " +
            		"from unlisted_programs up, program_types pt where " +  
        			"member_id = ? and up.program_type_id = pt.program_type_id order by (year + month)";
            try (PreparedStatement ps = connection.prepareStatement(sql)) {   
            	ps.setInt(1, member.id);
            	
                try (ResultSet resultSet = ps.executeQuery()) {                                        
                    while (resultSet.next()) {
                    	UnlistedProgram unlistedProgram = new UnlistedProgram();
                    	unlistedProgram.id = resultSet.getInt(1);
                    	unlistedProgram.programTypeId = resultSet.getInt(2);
                    	unlistedProgram.programType = resultSet.getString(3);
                    	unlistedProgram.month = resultSet.getInt(4);
                    	unlistedProgram.year = resultSet.getInt(5);
                    	unlistedProgram.teacher = resultSet.getString(6);
                    	unlistedProgram.venue = resultSet.getString(7);
                    	
                    	member.unlistedPrograms.add(unlistedProgram);
                    }
                }                                        
            }   
            
            return member;
            
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }                             
	}
	
	public static void add(ContactDetails contactDetails, int owningCenterId, String login) {
		
		AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);		
		SyntaxChecker.ensureValid(contactDetails);
		Validator.ensureValidCenter(owningCenterId);
		
		contactDetails.email = contactDetails.email.toLowerCase();
				
		if (null != get(contactDetails.email, login))
			 throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS, 
					 "Member [" + contactDetails.email + "] already exists");
		 
		try {                                
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {  
            	connection.setAutoCommit(false);
            	
            	String sql = "insert into members values (0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setString(1, contactDetails.firstName);                    
                    ps.setString(2, contactDetails.middleName);
                    ps.setString(3, contactDetails.lastName);
                    ps.setString(4, contactDetails.email);
                    ps.setString(5, contactDetails.phone);
                    ps.setString(6, contactDetails.homeAddress);
                    ps.setString(7, contactDetails.officeAddress);
                    ps.setString(8, contactDetails.homePhone);
                    ps.setString(9, contactDetails.officePhone);
                    ps.setString(10, contactDetails.singaporeNRIC);
                    
                    ps.executeUpdate();                                                                            
                }
                
                sql = "select member_id from members where email = ?";                          
                int memberId = 0;                                  
                try (PreparedStatement ps = connection.prepareStatement(sql)) { 
                	 ps.setString(1, contactDetails.email);
	            	 try (ResultSet resultSet = ps.executeQuery()) {                                        
	                     while (resultSet.next()) {
	                    	 memberId = resultSet.getInt(1);
	                     }
	            	 }
                }                
                                
                sql = "insert into member_centers values (?, ?, ?, ?)";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {
                	ps.setInt(1, memberId);
                	ps.setInt(2, owningCenterId);
                	ps.setBoolean(3, true);
                	ps.setBoolean(4, true);
                	
                	ps.executeUpdate();
                }
                
                connection.commit();
                connection.setAutoCommit(true);
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }             
	}
	
	static void addMany(List<ContactDetails> contactDetails, int owningCenterId, 
			String login) {
		
		AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);		
		SyntaxChecker.ensureValid(contactDetails);	
		
		List<String> emails = new ArrayList<>();
		for (ContactDetails contact : contactDetails) 			
			emails.add(contact.email);		
							 
        String sql = "insert into members (member_id, first_name, middle_name, " + 
        		"last_name, email, phone, home_address, office_address, home_phone, " +
        		"office_phone, singapore_nric) values (0, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";          
       
        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
            try (PreparedStatement ps = connection.prepareStatement(sql)) {            	
            	for (ContactDetails contact : contactDetails) {											                    
                    ps.setString(1, contact.firstName);
                    ps.setString(2, contact.middleName);
                    ps.setString(3, contact.lastName);
                    ps.setString(4, contact.email.toLowerCase());
                    ps.setString(5, contact.phone);
                    ps.setString(6, contact.homeAddress);
                    ps.setString(7, contact.officeAddress);
                    ps.setString(8, contact.homePhone);
                    ps.setString(9, contact.officePhone);
                    ps.setString(10, contact.singaporeNRIC);
                    
                    ps.addBatch();
            	}
            	connection.setAutoCommit(false);
            	ps.executeBatch();            	
            }
            
            List<Member> members = getMemberIdAndContactDetails(emails, connection);
            sql = "insert into member_centers (member_id, center_id, receive_email, owning_center) " +
            		"values (?, ?, ?, ?)";            
        	try (PreparedStatement ps = connection.prepareStatement(sql)) {          		
            	for (Member member : members) {		            		
            		ps.setInt(1, member.id);
            		ps.setInt(2, owningCenterId);
            		ps.setBoolean(3, true);
            		ps.setBoolean(4, true);	                    
                    
                    ps.addBatch();
            	}                	
            	ps.executeBatch();
            	connection.commit();  
            }            	            
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }             
	 }

	public static void updateContactDetails(int memberId, Member.ContactDetails contactDetails, String login) {
		String email = getEmailFromMemberId(memberId);
		
		if (email == null)
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"There is no member with id [" + memberId + "]");
		
		if (! login.equals(email))
			AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		
		SyntaxChecker.ensureValid(contactDetails);
		
        String sql = "update members set " + 
        		"first_name = ?, middle_name = ?, " +
        		"last_name = ?, email = ?, phone = ?, home_address = ?, " +
        		"office_address = ?, home_phone = ?, office_phone = ?, " + 
        		"singapore_nric = ? where member_id = ?";          
       
        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
            try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                
                ps.setString(1, contactDetails.firstName);
                ps.setString(2, contactDetails.middleName);
                ps.setString(3, contactDetails.lastName);
                ps.setString(4, contactDetails.email.toLowerCase());
                ps.setString(5, contactDetails.phone);
                ps.setString(6, contactDetails.homeAddress);
                ps.setString(7, contactDetails.officeAddress);
                ps.setString(8, contactDetails.homePhone);
                ps.setString(9, contactDetails.officePhone);
                ps.setString(10, contactDetails.singaporeNRIC);
                ps.setLong(11, memberId);
                
                ps.executeUpdate();                                                                            
            }                               
        }  catch (Exception ex) {
            throw new RuntimeException(ex);
        }         						
	}
	
	public static void updateManyContactDetails(List<Integer> memberIds, List<ContactDetails> contactDetails, 
			String login) {
		
		AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		SyntaxChecker.ensureValid(contactDetails);
				
		if (memberIds.size() != contactDetails.size()) 
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"List size mismatch. Num of members ids [" + memberIds.size() + 
					"] does not match no of contact details [" + contactDetails.size() + "]");
				
        String sql = "update members set " + 
        		"first_name = ?, middle_name = ?, " +
        		"last_name = ?, email = ?, phone = ?, home_address = ?, " +
        		"office_address = ?, home_phone = ?, office_phone = ?, " + 
        		"singapore_nric = ? where member_id = ?";          
       
        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
            try (PreparedStatement ps = connection.prepareStatement(sql)) {                 	
            	for (int i = 0; i < memberIds.size(); i++) {														
                    ps.setString(1, contactDetails.get(i).firstName);
                    ps.setString(2, contactDetails.get(i).middleName);
                    ps.setString(3, contactDetails.get(i).lastName);
                    ps.setString(4, contactDetails.get(i).email.toLowerCase());
                    ps.setString(5, contactDetails.get(i).phone);
                    ps.setString(6, contactDetails.get(i).homeAddress);
                    ps.setString(7, contactDetails.get(i).officeAddress);
                    ps.setString(8, contactDetails.get(i).homePhone);
                    ps.setString(9, contactDetails.get(i).officePhone);
                    ps.setString(10, contactDetails.get(i).singaporeNRIC);
                    ps.setLong(11, memberIds.get(i));
                    
                    ps.addBatch();
            	}
            	ps.executeBatch();
            }                               
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }         						
	}
	
	public static void addCenter(long memberId, long centerId, String login) {
		
		String email = getEmailFromMemberId(memberId);
		if (null == email)
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"There is no member with id [" + memberId + "]");
		
		if (! email.equals(login))
			AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		
		try {
            String sql = "insert into member_centers (member_id, center_id, receive_email, owning_center) values (?, ?, ?, ?)";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setLong(1, memberId);
                    ps.setLong(2, centerId);  
                    ps.setBoolean(3, true);
                    ps.setBoolean(4, false);
                    ps.executeUpdate();                                                                            
                }               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }          
	}
	
	public static void deleteCenter(int memberId, int centerId, String login) {
		
		Member member = get(memberId, login);
		
		if (null == member)
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"There is no member with id [" + memberId + "]");
		
		if (! login.equals(member.contactDetails.email))
			AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		
		if (centerId == member.owningCenter.id)
			throw new APIException(Status.ERROR_PRECONDITION_FAILURE, 
					"Cannot delete owning center [" + centerId + "] for member [" + memberId + "]");
				
		try {
            String sql = "delete from member_centers where member_id = ? and center_id = ?";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setLong(1, memberId);
                    ps.setLong(2, centerId);                    
                    ps.executeUpdate();                                                                            
                }               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }          
	}

	public static void addUnlistedProgram(long memberId, UnlistedProgram unlistedProgram, String login) {
				
		if (unlistedProgram.month < 1 || unlistedProgram.month > 12) 
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Month should be between 1 and 12");				
		
		if (unlistedProgram.year < 1970 || unlistedProgram.year > Calendar.getInstance().get(Calendar.YEAR)) 
			throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
					"Year should be after 1970 and lesser than or equal to current year");
		
		String email = getEmailFromMemberId(memberId);
		if (null == email)
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"There is no member with id [" + memberId + "]");
		
		if (! email.equals(login))
			AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
						
		try {
            String sql = "insert into unlisted_programs values (0, ?, ?, ?, ?, ?, ?)";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    
                    ps.setInt(1, unlistedProgram.programTypeId);
                    ps.setInt(2, unlistedProgram.month);                    
                    ps.setInt(3, unlistedProgram.year);
                    ps.setString(4, unlistedProgram.teacher);
                    ps.setString(5, unlistedProgram.venue);
                    ps.setLong(6, memberId);
                    ps.executeUpdate();                                                                            
                }               
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }  		
	}
	
	public static void deleteUnlistedProgram(int unlistedProgramId, String login) {	
		String email = getEmailFromUnlistedProgramId(unlistedProgramId, login);
		
		if (null == email)
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"There is no unlisted program with id [" + unlistedProgramId + "]");
		
		if (! email.equals(login))
			AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		
		try {
            String sql = "delete from unlisted_programs where unlisted_program_id = ?";          
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                                           
                    ps.setInt(1, unlistedProgramId);                   
                    ps.executeUpdate();                                                                            
                }                
            }           	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }  		
	}
	
	public static String addOrUpdateMany(int owningCenterId, List<ContactDetails> contactDetails, 
			String login) {
		
		AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		
		if (contactDetails.size() > Limits.Member.MAX_NUMRECORDS_BULKIMPORT)
			throw new APIException(Status.ERROR_OUT_OF_BOUNDS, 
					"No of records [" + contactDetails.size() + "] is greater than max allowed [" + 
							Limits.Member.MAX_NUMRECORDS_BULKIMPORT + "]");
		
		SyntaxChecker.ensureValid(contactDetails);
		
		List<String> emails = new ArrayList<>();
		Set<String> emailSet = new HashSet<>();
		for (ContactDetails contact : contactDetails) {
			contact.email = contact.email.toLowerCase(); //email always in lower case	
			if (emailSet.contains(contact.email))
				throw new APIException(Status.ERROR_RESOURCE_INCORRECTLY_SPECIFIED, 
						"Email [" + contact.email + "] is a duplicate");
			
			emails.add(contact.email);
			emailSet.add(contact.email);
		}
		
		List<Member> existing = getMemberIdAndContactDetails(emails, login);
		
		Map<String, Member> existingMap = new HashMap<>();		
		for (Member member : existing) 
			existingMap.put(member.contactDetails.email, member);
											
		List<ContactDetails> membersToBeUpdated = new ArrayList<>();
		List<ContactDetails> membersToBeInserted = new ArrayList<>();
		
		List<Integer> memberIds = new ArrayList<>();
		for (ContactDetails contact : contactDetails) {
			if (existingMap.containsKey(contact.email)) {
				Member member = existingMap.get(contact.email);
				membersToBeUpdated.add(applyChanges(member.contactDetails, contact));
				memberIds.add(member.id);
			}
			else 
				membersToBeInserted.add(contact);							
		}
		
		MemberRepository.addMany(membersToBeInserted, owningCenterId, login);						
		MemberRepository.updateManyContactDetails(memberIds, membersToBeUpdated, login);
		
		return "Inserted [" + membersToBeInserted.size() + "] records and updated [" +
			membersToBeUpdated.size() + "] records";		
	}
	
	static ContactDetails applyChanges(final ContactDetails contactDetails, ContactDetails changes) {		
		if ((changes.email != null) && (! changes.email.equals("")))
				contactDetails.email = changes.email;
		if ((changes.phone != null) && (! changes.phone.equals("")))
			contactDetails.phone = changes.phone;	
		if ((changes.firstName != null) && (! changes.firstName.equals("")))
			contactDetails.firstName = changes.firstName;
		if ((changes.middleName != null) && (! changes.middleName.equals("")))
			contactDetails.middleName = changes.middleName;
		if ((changes.lastName != null) && (! changes.lastName.equals("")))
			contactDetails.lastName = changes.lastName;
		if ((changes.homeAddress != null) && (! changes.homeAddress.equals("")))
			contactDetails.homeAddress = changes.homeAddress;
		if ((changes.homePhone != null) && (! changes.homePhone.equals("")))
			contactDetails.homePhone = changes.homePhone;
		if ((changes.officeAddress != null) && (! changes.officeAddress.equals("")))
			contactDetails.officeAddress = changes.officeAddress;
		if ((changes.officePhone != null) && (! changes.officePhone.equals("")))
			contactDetails.officePhone = changes.officePhone;
		if ((changes.singaporeNRIC != null) && (! changes.singaporeNRIC.equals("")))
			contactDetails.singaporeNRIC = changes.singaporeNRIC;
		
		return contactDetails;
	}
	
	static List<Member> getMemberIdAndContactDetails(List<String> emails, String login) {
		AccessController.ensurePrivilege(login, Privileges.EDIT_MEMBER);
		
		try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
			return getMemberIdAndContactDetails(emails, connection);
		} catch (Exception ex) {
            throw new RuntimeException(ex);
		}  
	}
	
	private static List<Member> getMemberIdAndContactDetails(List<String> emails, 
			Connection connection) throws SQLException {
		
		List<Member> members = new ArrayList<Member>();
		
		if (emails.size() == 0)
			return members;
		
		String sql = "select member_id, first_name, middle_name, last_name, email, " +
        		"phone, home_address, office_address, home_phone, office_phone, singapore_nric " + 
        		"from members where email in (";
		
		String questionMarks = "?";
		
		for (int i = 1; i < emails.size(); i++) {
			questionMarks += ", ?";				
		}
		
		sql += questionMarks + ")";			
														                
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
        	
        	for (int i = 0; i < emails.size(); i++) 
				ps.setString(i + 1, emails.get(i));					
        	         	 
        	 try (ResultSet resultSet = ps.executeQuery()) {                                        
                 while (resultSet.next()) {
                	 Member member = new Member();
                	 member.id = resultSet.getInt(1);
                	 member.contactDetails.firstName = resultSet.getString(2);	                    	 
                	 member.contactDetails.middleName = resultSet.getString(3);
                	 member.contactDetails.lastName = resultSet.getString(4);
                	 member.contactDetails.email = resultSet.getString(5);
                	 member.contactDetails.phone = resultSet.getString(6);
                	 member.contactDetails.homeAddress = resultSet.getString(7);
                	 member.contactDetails.officeAddress = resultSet.getString(8);
                	 member.contactDetails.homePhone = resultSet.getString(9);
                	 member.contactDetails.officePhone = resultSet.getString(10);
                	 member.contactDetails.singaporeNRIC = resultSet.getString(11);
                			 
                	 members.add(member);                	                 	 
                 }
        	 }        	 
        	 return members;
        }                       	
	}
	
	private static String getEmailFromMemberId(long memberId) {
		try {
            String sql = "select email from members where member_id = ?";          
           
            String email = null;
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) { 
                	 ps.setLong(1, memberId);
	            	 try (ResultSet resultSet = ps.executeQuery()) {                                        
	                     while (resultSet.next()) {
	                    	 email = resultSet.getString(1);
	                     }
	            	 }
                }                
            }         
            
            return email;
            
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }  
	}
		
	private static String getEmailFromUnlistedProgramId(long unlistedProgramId, String login) {
		try {
			String sql = "select email from members where member_id = (select member_id from unlisted_programs where unlisted_program_id = ?)";                     
           
            String email = null;
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) { 
                	 ps.setLong(1, unlistedProgramId);
	            	 try (ResultSet resultSet = ps.executeQuery()) {                                        
	                     while (resultSet.next()) {
	                    	 email = resultSet.getString(1);
	                     }
	            	 }
                }                
            }         
            
            return email;
            
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }  
	}
}



